#!/usr/bin/env python
#-*- coding: UTF-8 -*- 

#
# 此脚本请使用 python3 
#
# https://github.com/rory/apache-log-parser
#
#
# key {}
# https://blog.csdn.net/sinat_38682860/article/details/103989554
# https://www.cnblogs.com/paranoia/p/6178665.html
# url 
# https://blog.csdn.net/songlh1234/article/details/83790163
# https://www.cnblogs.com/xiao-xue-di/p/11843371.html
# https://www.cnblogs.com/stemon/p/6602185.html
# if 
# https://www.jb51.net/article/181346.htm
# https://blog.csdn.net/hh5820/article/details/80964247
# {} set 
# https://zhidao.baidu.com/question/1802579384993845227.html
# https://www.it1352.com/633294.html
# https://blog.csdn.net/weixin_39805734/article/details/110784484
# https://www.jb51.net/article/114969.htm
# 字典数据到mysql
# https://blog.csdn.net/weixin_41666747/article/details/82724758
# https://blog.csdn.net/weixin_39198406/article/details/78231430
# 
# data to file 
# https://www.cnblogs.com/apolloren/p/11168212.html
#
# {} empty 
# https://cloud.tencent.com/developer/article/1569560
#
# https://github.com/soulsidd/LogParser/blob/master/logparser.py
# https://github.com/syedaali/apacher/blob/master/apacher.py
#
# 遍历当前目录，获取文件列表
# https://github.com/injetlee/Python/blob/master/countFile.py
#
# excelToDatabase
# https://github.com/injetlee/Python/blob/master/excelToDatabase.py
#
# ModuleNotFoundError: No module named 'pymysql'
# 
# pip3 install PyMySQL
# import pymysql
#
# time 
# https://www.cnblogs.com/zhangxinqi/p/7687862.html
#
# Python从数据库读取大量数据批量写入文件
# https://blog.csdn.net/Tan_HandSome/article/details/79261413
# 用列表分片和while循环
# https://bbs.csdn.net/topics/397066266?list=69385203
# python3的取余操作
# https://www.cnblogs.com/chulin/p/10032362.html
#
# 为了维护的目的，我需要知道SQL数据库发送完整的SQL字符串
# http://www.voidcn.com/article/p-mtnahdpv-bsx.html
# import MySQLdb
# mc = MySQLdb.connect()
# r = mc.cursor()
# r.execute('select %s, %s', ("foo", 2))
# r._executed
# "select 'foo', 2"

# import psycopg2
# pc = psycopg2.connect()
# r = pc.cursor()
# r.execute('select %s, %s', ('foo', 2))
# r.query
# "select E'foo', 2"
# 
# 
# Python中 str、bytes、bytearray的相互转化  -cursor._executed- 
# https://blog.csdn.net/doudoudouzoule/article/details/85335268
# 
# print('-cursor._executed-',cursor._executed)
# bytearray(b"insert into ce_db.ce_user (uid,uv,status,create_time) values(\'11811\',1,1,\'2021-04-12 18:25:36\'),(\'11811\',1,1,\'2021-04-12 18:25:36\')")
# 获取实际执行的sql 并转化成易读形式
# print('-cursor._executed-',cursor._executed.decode('utf-8'))
# insert into ce_db.ce_user (uid,uv,status,create_time) values('11811',1,1,'2021-04-12 18:20:53'),('11811',1,1,'2021-04-12 18:20:53')
# 
# open
# https://www.w3school.com.cn/python/ref_func_open.asp
#
#

"""
recommenduid=1

config = {
    'host': '39.100.119.163',
    'port':3306,
    'user': 'emei',
    'password': 'emei123',
    'charset': 'utf8mb4',
}

# 打开数据库连接
conn = pymysql.connect(**config)
db_name = 'lyexcel_db'

# conn.autocommit(1)

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = conn.cursor()

# 使用 execute()  方法执行 SQL 
cursor.execute('create database if not exists %s' %db_name)
# 选择数据库
conn.select_db(db_name)
table_name = 'info'
cursor.execute('create table if not exists %s(id MEDIUMINT NOT NULL AUTO_INCREMENT,name varchar(30),tel varchar(30),primary key (id))'%table_name)

value1=(cell[0].value,cell[4].value)
cursor.execute('insert into info (name,tel) values(%s,%s)',value1)

# 使用 execute()  方法执行 SQL 查询 
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print ("Database version : %s " % data)


cursor.execute("show databases")
results = cursor.fetchall()
print(results)
# (('information_schema',)，('mysql',))




# 使用 execute()  方法执行 SQL 查询 
cursor.execute("show tables")
# 获取所有记录列表
results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
       # 打印结果
      print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income ))

# 使用预处理语句创建表
sql = """
"""CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

"""
 
cursor.execute(sql)



# 关闭数据库连接
conn.close()

"""
#
#
#
#
#
#
#

import apache_log_parser
from pprint import pprint
import sys
import os
import urllib.parse
import pymysql
import time
from datetime import datetime
import argparse



# 00 01 * * * sh /data/data_flow/user_pv/datePvSql.sh > /data0/data_flow/emei_cronlog/user_pv.log 2>&1
# 00 01 * * * python3 /data/data_flow/user_pv/user_pv.py > /data0/data_flow/emei_cronlog/user_pv.log 2>&1

file_name="/Users/mac/zwn/zwn_git/simple_shell_course/apache_log/zwnPython/log/m-access_log_2021-04-05"
path_logs='/Users/mac/zwn/zwn_git/simple_shell_course/apache_log/zwnPython/log/'
recommenduid_key='recommenduid'
recommendUidData = {}
recommenduid_pv_data={}
statusOk = '200'

operation_time=None
file_recommenduid=None
current_time=None
write_file=None





db_name = 'rebate_db'
config = {
    'host': '39.100.119.163',
    'port':3306,
    'user': 'emei',
    'password': 'emei123',
    'charset': 'utf8mb4',
    'db':db_name
}
table_name = 'rebate_user_pv'
yesterday_time = time.time() - 1*24*60*60
current_ymd=time.strftime('%Y-%m-%d',time.localtime( time.time() ))
yesterday_ymd=time.strftime('%Y-%m-%d',time.localtime( yesterday_time ))
conn = pymysql.connect(**config)
cursor = conn.cursor()





# 得到现在的时间
# now = datetime.datetime.now()
now = datetime.now()
# YYYY-MM-DD日期格式
date_now = now.date()

#调用日志记录函数
# file_name = '日志文件名_' + str(date_now) + '.log'
# print(file_name)


"""
import argparse
# parser.add_argument 方法的type参数理论上可以是任何合法的类型， 但有些参数传入格式比较麻烦，例如list，所以一般使用bool, int, str, float这些基本类型就行了，更复杂的需求可以通过str传入，然后手动解析。bool类型的解析比较特殊，传入任何值都会被解析成True，传入空值时才为False
# mac@macdeMacBook-Pro zwnPython % python3 manlog.py  -gpus 1,2,9 --batch-size 45 --float-size 99.08 --bool-size ''
# mac@macdeMacBook-Pro zwnPython % python3 manlog.py  -gpus 12 --batch-size 45 --float-size 99.08 --bool-size ''  -str-gpus pp
parser = argparse.ArgumentParser(description='manual to this script')
parser.add_argument("-gpus", type=str, default="0")
parser.add_argument("-str-gpus", type=str, default="0")
parser.add_argument("--batch-size", type=int, default=32)
parser.add_argument("--float-size", type=float, default=0.12)
parser.add_argument("--bool-size", type=bool, default=True)

args = parser.parse_args()


gpus = sys.argv[1]
print('第一个参数'+gpus)

print('gpus参数'+args.gpus)
print("参数列表",args)

"""


# Python--报错TabError: inconsistent use of tabs and spaces in indentation
# 在运行flask框架的时候报错，死活看不出来，看到资料说是由于混用了tab键和4个空格键造成的，只用一个就可以解决这个问题



# https://github.com/rory/apache-log-parser
# line_parser = apache_log_parser.make_parser("%h <<%P>> %t %Dus \"%r\" %>s %b  \"%{Referer}i\" \"%{User-Agent}i\" %l %u")
# strData = '127.0.0.1 <<6113>> [16/Aug/2013:15:45:34 +0000] 1966093us "GET / HTTP/1.1" 200 3478  "https://example.com/" "Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.18)" - -'
# log_line_data = line_parser(strData)



# pprint(log_line_data)



# 要和 LogFormat 配置的顺序保持一致
line_parser = apache_log_parser.make_parser("%h \"%r\" %>s %b")
# line_parser = apache_log_parser.make_parser("%h %{HTTP_HOST}e %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-agent}i\" %{UID}C %{SESSIONID}C")
# strData = '121.42.57.194 yx.emei123.com - [05/Apr/2021:01:49:50 +0800] "POST /message/onlinereport HTTP/1.1" 200 72 "-" "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; GTB0.0; InfoPath.2; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022)" - -'
# log_linet_datat = line_parser(strData)
# pprint(log_linet_datat)
log_linet_datat = """
{'cookie_sessionid': '',
 'cookie_uid': '-',
 'env_http_host': 'm.emei123.com',
 'remote_host': '66.249.76.144',
 'remote_user': '-',
 'request_first_line': 'GET /product/productdetail?pid=979&recommenduid= '
                       'HTTP/1.1',
 'request_header_referer': '-',
 'request_header_user_agent': 'Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X '
                              'Build/MMB29P) AppleWebKit/537.36 (KHTML, like '
                              'Gecko) Chrome/89.0.4389.102 Mobile '
                              'Safari/537.36 (compatible; Googlebot/2.1; '
                              '+http://www.google.com/bot.html)',
 'request_http_ver': '1.1',
 'request_method': 'GET',
 'request_url': '/product/productdetail?pid=979&recommenduid=',
 'request_url_fragment': '',
 'request_url_hostname': None,
 'request_url_netloc': '',
 'request_url_password': None,
 'request_url_path': '/product/productdetail',
 'request_url_port': None,
 'request_url_query': 'pid=979&recommenduid=',
 'request_url_query_dict': {'pid': ['979']},
 'request_url_query_list': [('pid', '979')],
 'request_url_query_simple_dict': {'pid': '979'},
 'request_url_scheme': '',
 'request_url_username': None,
 'response_bytes_clf': '16861',
 'status': '200',
 'time_received': '[05/Apr/2021:19:19:27 +0800]',
 'time_received_datetimeobj': datetime.datetime(2021, 4, 5, 19, 19, 27),
 'time_received_isoformat': '2021-04-05T19:19:27',
 'time_received_tz_datetimeobj': datetime.datetime(2021, 4, 5, 19, 19, 27, tzinfo='0800'),
 'time_received_tz_isoformat': '2021-04-05T19:19:27+08:00',
 'time_received_utc_datetimeobj': datetime.datetime(2021, 4, 5, 11, 19, 27, tzinfo='0000'),
 'time_received_utc_isoformat': '2021-04-05T11:19:27+00:00'}
"""
# print(log_linet_datat['status'],log_linet_datat['request_url'])
# result = urllib.parse.urlsplit(log_linet_datat['request_url'])
# print(result)
# result = SplitResult(scheme='', netloc='', path='/product/productdetail', query='pid=1280', fragment='')
# print(result['query'])

# 读取文件内容并打印
def readLinesFile(filename,num):
	f = open(filename, 'r') # r 代表read
	if not num:
		num = 100
	while 1:
		lines = f.readlines(num)
		if not lines:
			break
		for line in lines:
			print(line)
	f.close()

# 日志格式化
def log_line_data_def(dataLine):
    log_line_data = line_parser(dataLine)
    pprint(log_line_data)
    # 获取URL 
    url = log_line_data['request_url']
    status = log_line_data['status']
    # result = urllib.parse.urlsplit(url)
    # query = dict(result.query)
    # print(result.query)
    # 获取URL的参数
    query = dict(urllib.parse.parse_qsl(urllib.parse.urlsplit(url).query))
    if query:
        # pprint(query)
        recommendUidDataSet(query,status)

    # if query:
    #     pprint(query)
    # else:
    #     pprint('-空-')
    # pprint(query['recommenduid'])
    # pprint(log_line_data)
    # print(log_line_data['status'],log_line_data['request_url'])
    # print(log_line_data['status'],log_line_data['request_url'],'      ',log_line_data['request_header_referer'])
    # pprint(log_line_data['status'],log_line_data['request_url'])
    # recommendUidDataSet(query)

def recommendUidDataSet(query,status):
    # exitKey = 'recommenduid' in query.keys()
    exitKey = recommenduid_key in query.keys() 
    if exitKey and status == statusOk :
        # pprint(query) # 字符串和字典不能同时存在此行 ， 打印格式
        # print(status,query)
        exitKeyVal = query[recommenduid_key] in recommendUidData.keys()
		# 
        # print( exitKeyVal )
        # recommendUidData[query[recommenduid_key]] +=1
        if not(exitKeyVal) :
            # print( '-假-' , exitKeyVal )
            recommendUidData[query[recommenduid_key]]=1
            # print( '-真-' , exitKeyVal,recommendUidData )
        else :
            recommendUidData[query[recommenduid_key]] +=1
            # print( '-真-' , exitKeyVal,recommendUidData )
        
        # if exitKeyVal :
            # print( '-真-' , exitKeyVal )
        # else :
            # print( '-假-' , exitKeyVal )



# 日志格式化
def log_line_data(data_line):
    parser_line_data = line_parser(data_line)
    # pprint(parser_line_data)
    if parser_line_data['status'] != statusOk :
       return
    # 获取URL 
    url = parser_line_data['request_url']
    # status = parser_line_data['status']
    # result = urllib.parse.urlsplit(url)
    # query = dict(result.query)
    # print(result.query)
    # 获取URL的参数
    query = dict(urllib.parse.parse_qsl(urllib.parse.urlsplit(url).query))
    if query:
        pprint(query)
        # recommendUidDataSet(query,status)

def recommendUidDataSetd(query,status):
    # exitKey = 'recommenduid' in query.keys()
    exitKey = recommenduid_key in query.keys() 
    if exitKey and status == statusOk :
        # pprint(query) # 字符串和字典不能同时存在此行 ， 打印格式
        # print(status,query)
        exitKeyVal = query[recommenduid_key] in recommendUidData.keys()
		# 
        # print( exitKeyVal )
        # recommendUidData[query[recommenduid_key]] +=1
        if not(exitKeyVal) :
            # print( '-假-' , exitKeyVal )
            recommendUidData[query[recommenduid_key]]=1
            # print( '-真-' , exitKeyVal,recommendUidData )
        else :
            recommendUidData[query[recommenduid_key]] +=1
            # print( '-真-' , exitKeyVal,recommendUidData )
        
        # if exitKeyVal :
            # print( '-真-' , exitKeyVal )
        # else :
            # print( '-假-' , exitKeyVal )




# 读取文件内容并打印
def readFile(filename):
  # print(filename)
  fopen = open(filename, 'r') # r 代表read
  for eachLine in fopen:
    # print( eachLine)
    log_line_data_def(eachLine)

            
    # print(  "读取到得内容如下：",eachLine )
  fopen.close()

# 读取文件内容
def read_file(filename):
  # print(filename)
  fopen_obj = open(filename, 'r') # r 代表read
  for each_line in fopen_obj:
    # print( each_line) # 日志格式化
    parser_line_data = line_parser(each_line)
    # print('---',parser_line_data,"")
    # print("")
    # 去除http状态不是200
    if parser_line_data['status'] != statusOk :
       continue
    # print('---',parser_line_data)
    # 获取URL 
    url = parser_line_data['request_url']
    # 获取URL的参数
    query = dict(urllib.parse.parse_qsl(urllib.parse.urlsplit(url).query))
    # exitKey = recommenduid_key in query
    # 去除参数中不含recommenduid_key变量值
    if recommenduid_key not in query :
       continue
    # print("-带有recommenduid_key变量的-",query,"")
    # print("")
    # print("-数据组装 --> {'11814': 5, '11816': 1}-")
    if query[recommenduid_key] not in recommenduid_pv_data :
        recommenduid_pv_data[query[recommenduid_key]]=0
    recommenduid_pv_data[query[recommenduid_key]] += 1
    # print("-recommenduid_pv_data-",recommenduid_pv_data,"")
    # print("")
  fopen_obj.close()



# 遍历指定目录，显示目录下的所有文件名
def eachFile(filepath):
  pathDir = os.listdir(filepath)
  for allDir in pathDir:
    if  yesterday_ymd in allDir :
        # 取出指定日期的文件
        child = os.path.join('%s%s' % (filepath, allDir))
        # readLinesFile( child,100 )
        # 读取文件内容
        readFile(child)
        # print ( child.decode('gbk') ) # .decode('gbk')是解决中文显示乱码问题
        # print( child )


# 遍历指定目录，显示目录下的所有文件名
def list_dir(filepath):
  for dir_list in os.listdir(filepath):
    child = os.path.join('%s%s' % (filepath, dir_list))
    # readLinesFile( child,100 )
    # 读取文件内容
    # readFile(child)
    # print ( child.decode('gbk') ) # .decode('gbk')是解决中文显示乱码问题
    print( child )



# 输入多行文字，写入指定文件并保存到指定文件夹
def writeFile(filename):
  fopen = open(filename, 'w')
  print(  "\r请任意输入多行文字"," ( 输入 .号回车保存)" )
  while True:
    aLine = raw_input()
    if aLine != ".":
      fopen.write('%s%s' % (aLine, os.linesep))
    else:
      print( "文件已保存!")
      break
  fopen.close()



# 数据展示
def showData(data):
    for eachLine in data:
        print(eachLine , data[eachLine])

# 写数据
def setDataToMysql():
    c_time = time.time() - 1*24*60*60
    current_day=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime( time.time() ))
    operation_time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime( c_time ))
    print (current_day,operation_time)
    print ("current_day : %s ,operation_time :%s " % (current_day,operation_time))
    conn = pymysql.connect(**config)
    print (conn)
    # conn.select_db(db_name)
    cursor = conn.cursor()
    
    cursor.execute("select version()")
    data = cursor.fetchone()
    print ("Database version : %s " % data)
    
    pprint ('')

    # select_sql="select * from %s where "
    cursor.execute("show tables")
    # 获取所有记录列表
    results = cursor.fetchall()
    print (results)

    pprint ('')

    cursor.execute("select * from rebate_user_pv order by  rupid desc ")
    # results = cursor.fetchall()
    data = cursor.fetchone()
    pprint (data)
    # results = cursor.fetchall()
    # pprint (results)

    pprint ('')

    cursor.execute("select * from rebate_user_pv where uid=%s",(888888))
    # results = cursor.fetchall()
    data = cursor.fetchone()
    pprint (data)

    pprint ('')

    cursor.execute("desc rebate_db.rebate_user_pv;")
    results = cursor.fetchall()
    pprint (results)

    #另一种插入数据的方式，通过字符串传入值 uid pv status create_time update_time operation_time
    sql="insert into rebate_db.rebate_user_pv (uid,pv,status,create_time,update_time,operation_time) values(%s,%s,%s,%s,%s,%s)"
    rows = cursor.execute(sql,(888888,7,1,current_day,current_day,operation_time))
    pprint ('-写入影响的行数：%s-' %(rows))

    cursor.close()#先关闭游标
    conn.commit() # 提交数据的增删改 没有此 增删改 不起作用
    conn.close()#再关闭数据库连接



def close_conn():
    cursor.close()
    conn.close()

# 执行sql语句 并记录日志
def pymysql_executemany(sql,data):
    # print(sql,data) # https://blog.51cto.com/ylxhzjw/1432969
    # cursor.execute('set profiling = 1')
    # print("")
    cursor.executemany(sql,data)
    # print('-cursor-',cursor)
    # http://www.voidcn.com/article/p-mtnahdpv-bsx.html
    # 获取实际执行的sql 并转化成易读形式
    sql_execute=cursor._executed.decode('utf-8')
    # print('-cursor._executed-',cursor._executed.decode('utf-8'))
    #cursor.execute('show profiles')
    #for row in cursor:
    #    print("-cursor.row-",row) 
    # number_rows_inserted = cursor.rowcount
    # print('-number_rows_inserted-',number_rows_inserted)
    # print("")
    # cursor.execute('set profiling = 0')
    # conn.commit()
    # sql 语句写入到文件
    fopen = open(write_file, 'a')
    fopen.write('%s%s' % (sql_execute,"\n"))
    fopen.close()




# pv 数据写入数据库
def insert_pv_data(data):
    insert_list = []
    data_length = len(data)
    i = 0
    # print('--data_length--',data_length)
    # print('--data--',data)
    # print('--current_time--',current_time,'--operation_time--',operation_time)
    # print("")
    # print("\r\n")
    # c_time = time.time() - 1*24*60*60
    # operation_time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime( c_time ))
    # operation_day=time.strftime('%Y-%m-%d',time.localtime( c_time ))
    for uid in data:
        # current_time=time.strftime('%Y-%m-%d %H:%M:%S',time.localtime( time.time() ))
        sql="insert into rebate_db.rebate_user_pv (uid,pv,status,create_time,update_time,operation_time) values(%s,%s,%s,%s,%s,%s)"
        insert_list.append((uid,data[uid],1,current_time,current_time,operation_time))
        i +=1
        if i%6 == 0 or i == data_length:
            # pprint(insert_list)
            # 写入sql 语句记录日志
            pymysql_executemany(sql,insert_list)
            insert_list = []
            # print("")
            # print("\r\n")



def main():
    try:
        # 读取文件内容并设置数据
        read_file(file_recommenduid)
        # print(recommenduid_pv_data)
        # print("")
        
        # setDataToMysql()
        # eachFile(dirFile)
        # readFile(file_name)
        # showData(recommenduid_pv_data)
        # pv 数据写入数据库
        insert_pv_data(recommenduid_pv_data)
        close_conn()
        # print(yesterday_ymd)
    except ValueError:
        close_conn()






if __name__ == '__main__':
    # 获取参数 
    parser = argparse.ArgumentParser(description='manual to this script')
    parser.add_argument("--file_recommenduid", type=str, default="")
    parser.add_argument("--operation_time", type=str, default="")
    parser.add_argument("--current_time", type=str, default="")
    parser.add_argument("--write_file", type=str, default="")

    args = parser.parse_args()
    # print(args)
    file_recommenduid = args.file_recommenduid
    operation_time=args.operation_time
    current_time=args.current_time
    write_file=args.write_file
    main()



# 单行读取
# f = open(file_name)             # 返回一个文件对象  
# line = f.readline()             # 调用文件的 readline()方法  
# while line:  
    #print line,                 # 在 Python 2中，后面跟 ',' 将忽略换行符  
    # print(line, end = '')       # 在 Python 3中使用
    
    # line_parser(line)
    
    #line = f.readline()
#f.close()


